package com.pitaya.winwine.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.pitaya.winwine.domain.Topic;
import com.pitaya.winwine.domain.User;

@NoRepositoryBean
public class TopicRepositoryImpl extends JdbcDaoSupport implements TopicRepositoryCustom {
	
	private static final String SQL_RECOMMEND = "SELECT t.id, t.title, u.username, t.editTime, t.imageUrl, t.summary, t.tagStr FROM (SELECT DISTINCT topicId FROM r_topic_tag tt WHERE EXISTS (SELECT tagId FROM r_user_tag ut WHERE userId=? AND tt.tagId=ut.tagId)) r INNER JOIN tb_topic t ON r.topicId=t.id INNER JOIN tb_user u ON t.userId=u.id ORDER BY t.clickCount DESC LIMIT ?";

	@Autowired
	public TopicRepositoryImpl(DataSource dataSource) {
		setDataSource(dataSource);
	}
	
	@Override
	public List<Topic> findRecommedByLimit(Integer userId, int limit) {
		return getJdbcTemplate().query(SQL_RECOMMEND, new Object[]{userId, limit}, new TopicRowMapper());
	}
	
	private static class TopicRowMapper implements ParameterizedRowMapper<Topic> {

		@Override
		public Topic mapRow(ResultSet rs, int rowNum) throws SQLException {
			Topic topic = new Topic();
			topic.setId(rs.getInt("id"));
			topic.setTitle(rs.getString("title"));
			User user = new User();
			user.setUsername(rs.getString("username"));
			topic.setUser(user);
			topic.setEditTime(rs.getTimestamp("editTime"));
			topic.setImageUrl(rs.getString("imageUrl"));
			topic.setSummary(rs.getString("summary"));
			topic.setTagStr(rs.getString("tagStr"));
			return topic;
		}
		
	}

}
